<?php

namespace App\Models\V2;

use DB;

class BookModel
{

    protected $bookColum = [
        "BookID",
        "TypeID",
        "BookTitle",
        "BookDesc",
        'ppt',
        "book_img as FilePath1",
        "mp4 as FilePath2",
        "mp3 as FilePath3",
        "SortNumber",
        "IsGratis",
        "IsShow",
        "BookRecommend",
        "ReadNumber",
        "SearchIDList",
        "MasterID",
        "DeleteMark",
        "PageTitle",
        "Keywords",
        "Description",
        "ReleaseDate",
        "AddDate",
        "publish_date",
        'author',
    ];

    /**
     * 获取书籍类型列表
     * */
    public function get_book_types($type = 0)
    {
        $bookTypes = DB::table('BookType')
            ->select('TypeID', 'TypeName', 'SortNumber','icon_img')
            ->where('IsShow', '1')
            ->where('DeleteMark', '1');
        $type > 0 && $bookTypes->where('ParentID',$type);
        $bookTypes = $bookTypes->orderBy('SortNumber', 'desc')->get();
        $bookTypes = $bookTypes ? $bookTypes : [];
        foreach($bookTypes as $nav)
        {
            $nav->icon_img && $nav->icon_img = env('HOST')."/".$nav->icon_img;
        }
        return $bookTypes;
    }

    /**
     * 获取热门书籍
     *
     * */
    public function get_hot_books()
    {
        /*广告*/
        $hotBooks = DB::table('bookhots')
            ->select('BoHoID', 'LinkUrl', 'FilePath1', 'BoHoTitle', 'SortNumber')
            ->where('BoHoType', '1')
            ->where('IsShow', '1')
            ->where('DeleteMark', '1')
            ->orderBy('SortNumber', 'desc')
            ->take(5)
            ->get();

        foreach ($hotBooks as $key) {
            $key->FilePath1  = env('ADMIN_HOST') . $key->FilePath1;
            $key->urlAddress = env('ADMIN_HOST') . "/hotdetail/" . $key->BoHoID . ".html";
        }
        return $hotBooks;
    }


    /**
     * 获得推荐书籍
     * */
    public function get_recomment_books()
    {
        /*推荐书籍*/
        $recommentBooks = DB::table('bookinfo')
            ->select('BookID', 'FilePath1', 'BookTitle', 'BookDesc', 'SortNumber', 'IsGratis', 'AddDate')
            ->where('BookRecommend', '1')
            ->where('IsShow', '1')
            ->where('DeleteMark', '1')
            ->orderBy('SortNumber', 'desc')
            ->take(3)
            ->get();

        foreach ($recommentBooks as $key) {
            $key->FilePath1 = env('ADMIN_HOST') . $key->FilePath1;
            /* if ($key->IsGratis == 0) {
                 if ($mstate == 1) {
                     $key->IsGratis = 1;
                 } else {
                     $key->IsGratis = 0;
                 }
             }*/
        }
        return $recommentBooks;
    }


    /**
     * 获取评论信息的详细内容
     * */
    public function get_comment_info($commentId)
    {
        $colum        = [
            "a.BookID",
            "a.SumUpID",
            "a.MemNumber",
            "a.is_like_users",
            "b.UserID",
            "b.UserName",
            "b.NickName",
            "b.YourName",
            "b.FilePath1",
            "d.BookTitle",
            "a.BookReview1",
            "a.BookReview2",
            "a.BookReview3",
            "a.BookReview4",
            "a.AddDate"
        ];
        $readPlanInfo = DB::table("booksumup as a")
            ->leftJoin("memberinfo as b", 'b.MemNumber', '=', 'a.MemNumber')
            ->leftJoin('bookinfo as d', 'd.BookID', '=', 'a.BookID')
            ->select($colum)
            ->where("a.SumUpID", $commentId)
            ->first();
        if ($readPlanInfo) {
            $readPlanInfo->FilePath1 = change_member_head_img($readPlanInfo->FilePath1);
        }
        return $readPlanInfo;
    }


    /**
     * 获取书籍列表
     * 无效书籍 未发布，已删除的 隐藏显示
     * @param integer page 页数
     * @param boolean onlyValid 是否只需要有效的
     * */
    public function get_book_list($page = 0, $onlyValid = false,$videoType="")
    {

        $bookDB = DB::table('bookinfo');
        $date   = date('Y-m-d', time());

        if ($onlyValid == true) {
            $condition = [
                ['DeleteMark', '=', 1],
                ['publish_date', '<=', $date]
            ];
            $bookDB->where('IsShow', 1)->where($condition);
        }

        if($videoType)
        {
            $bookDB->where('video_type',$videoType);
        }
        if ($page !== 0) {
            $page       = $page > 0 ? $page : 1;
            $pageNumber = env('PAGE_NUMBER', 20);
            $skip       = ($page - 1) * $pageNumber;
            $bookDB->skip($skip)->take($pageNumber);
        }

        $bookList = $bookDB->select($this->bookColum)
            ->orderBy('SortNumber', 'asc')
            ->orderBy('AddDate', 'desc')
            ->get();

        foreach ($bookList as $book) {
            $book->FilePath1 = env('ADMIN_HOST') . $book->FilePath1;
            $book->MasterID  = 0;
        }
        return $bookList;
    }


    /**
     * 获取用户收集的书籍
     * @param integer memberNumber  用户编号
     * @return object
     * */
    public function get_member_collected_book($memberNumber)
    {
        $colums = $this->bookColum;
        foreach ($colums as $key => $v) {
            $colums[$key] = "b." . $v;
        }
        array_push($colums, "a.ColID");

        $bookList = DB::table("bookcollect as a")
            ->leftJoin("bookinfo as b", "b.BookID", "=", "a.BookId")
            ->select($colums)
            ->where('MemNumber', $memberNumber)
            ->get();

        $bookList = count($bookList) > 0 ? $bookList : [];
        return $bookList;
    }


    /**
     * 获取书籍信息
     * @param integer bookId 书籍ID
     * @return object
     * */
    public function get_book_info($bookId)
    {
        $bookInfo = DB::table('bookinfo')
            ->select($this->bookColum)
            ->where('DeleteMark', '1')
            ->where('IsShow', '1')
            ->where('BookID', $bookId)
            ->first();

        $bookInfo->FilePath1 = env('ADMIN_HOST') . $bookInfo->FilePath1;
        $bookInfo->AddDate  = $bookInfo->publish_date;
        return $bookInfo;
    }

    /**
     * 获取最新的一本书
     * @return object
     * */
    public function get_last_book_info()
    {
        $date   = date('Y-m-d',time());
        $bookInfo            = DB::table('bookinfo')
            ->select($this->bookColum)
            ->where('DeleteMark', '1')
            ->where('IsShow', '1')
            ->where('video_type','lhdd')
            ->where('publish_date',"<=",$date)
            ->orderBy('AddDate', 'desc')
            ->first();

        $bookInfo->FilePath1 = env('ADMIN_HOST') . $bookInfo->FilePath1;
        $bookInfo->AddDate   = $bookInfo->publish_date;
        return $bookInfo;
    }


    /**
     * 获得某日的书籍
     * @param string $date 日期
     * */
    public function get_date_book($date)
    {
        $bookInfo = DB::table('bookinfo')
            ->select($this->bookColum)
            ->where('DeleteMark', 1)
            ->where('IsShow', 1)
            ->where('publish_date', $date)
            ->first();

        return $bookInfo;
    }


    /**
     * 获取读书会会员的读书改进计划
     * @param integer bookId 书籍ID
     * @param integer readPartyId 读书会ID
     * @param integer departmentId 部门ID 0:其他  -1:全部
     * @return object
     * */
    public function get_read_plan_by_book_readparty_v2($bookId, $readPartyId, $departmentId = -1, $type = 1)
    {
        $colum = [
            "d.SumUpID",
            "d.BookID",
            "d.MemNumber",
            "d.BookReview1",
            "d.BookReview2",
            "d.BookReview3",
            "d.BookReview4",
            "d.AddDate",
            "d.is_like_users",
            "b.YourName",
            "b.FilePath1",
            "b.UserID",
        ];


        if ($type == 1) {//企业
            $db = DB::table('read_party_member as a')
                ->leftJoin('read_party as p', 'p.ReaParID', '=', 'a.read_party_id')
                ->leftJoin('memberinfo as b', 'b.UserID', '=', 'a.user_id')
                ->leftJoin('company_user as k', function ($join) {
                    $join->on('k.user_id', '=', 'a.user_id')->on('k.company_id', '=', 'p.company_id');
                })
                ->leftJoin('booksumup as d', 'd.MemNumber', '=', 'b.MemNumber')
                ->select($colum)
                ->where('d.BookID', $bookId)
                ->where('a.read_party_id', $readPartyId)
                ->where('a.is_delete', 0)
                ->where('d.is_delete', 0)
                ->where('k.is_delete', 0);
        } else {//个人

            $db = DB::table('read_party_member as a')
                ->leftJoin('memberinfo as b', 'b.UserID', '=', 'a.user_id')
                ->leftJoin('booksumup as d', 'd.MemNumber', '=', 'b.MemNumber')
                ->select($colum)
                ->where('d.BookID', $bookId)
                ->where('d.is_delete', 0)
                ->where('a.read_party_id', $readPartyId)
                ->where('a.is_delete', 0);
        }

        if ($departmentId > -1) {
            $db->where('k.department_id', $departmentId);
        }

        $readPlans = $db->orderBy('d.AddDate', "desc")
            ->simplePaginate(20);


        foreach ($readPlans as $plan) {
            $plan->FilePath1   = change_member_head_img($plan->FilePath1);
            $plan->BookReview1 = emoji_text_decode($plan->BookReview1);
            $plan->BookReview2 = emoji_text_decode($plan->BookReview2);
            $plan->BookReview3 = emoji_text_decode($plan->BookReview3);
            $plan->BookReview4 = emoji_text_decode($plan->BookReview4);
        }
        return $readPlans;
    }




    /**
     * 获取读书会会员的读书改进计划
     * @param integer bookId 书籍ID
     * @param integer readPartyId 读书会ID
     * @param integer departmentId 部门ID 0:其他  -1:全部
     * @param integer $firstReadPlanId   从某条读书改进计划开始
     * @param integer $type 读书会类型
     * @return object
     * */
    public function get_read_plan_by_book_readparty($bookId, $readPartyId, $departmentId = -1, $type = 1,$firstReadPlanId=0)
    {
        $colum = [
            "d.SumUpID",
            "d.BookID",
            "d.MemNumber",
            "d.BookReview1",
            "d.BookReview2",
            "d.BookReview3",
            "d.BookReview4",
            "d.AddDate",
            "d.is_like_users",
            "b.YourName",
            "b.FilePath1",
            "b.UserID",
        ];
        if ($type == 1) {//企业
            array_push($colum,'k.department_id');
            $db = DB::table('read_party_member as a')
                ->leftJoin('read_party as p', 'p.ReaParID', '=', 'a.read_party_id')
                ->leftJoin('memberinfo as b', 'b.UserID', '=', 'a.user_id')
                ->leftJoin('company_user as k', function ($join) {
                    $join->on('k.user_id', '=', 'a.user_id')->on('k.department_id', '=', 'a.department_id');
                })
                ->leftJoin('booksumup as d', 'd.MemNumber', '=', 'b.MemNumber')
                ->select($colum)
                ->where('d.BookID', $bookId)
                ->where('b.DeleteMark',1)
                ->where('a.read_party_id', $readPartyId)
                ->where('a.is_delete', 0)
                ->where('d.is_delete', 0)
                ->where('k.is_delete', 0);

            if ($departmentId > -1) {
                $db->where('k.department_id', $departmentId);
            }

        } else {//个人
            $db = DB::table('read_party_member as a')
                ->leftJoin('read_party as p', 'p.ReaParID', '=', 'a.read_party_id')
                ->leftJoin('memberinfo as b', 'b.UserID', '=', 'a.user_id')
                ->leftJoin('booksumup as d', 'd.MemNumber', '=', 'b.MemNumber')
                ->select($colum)
                ->where('d.BookID', $bookId)
                ->where('b.DeleteMark',1)
                ->where('d.is_delete', 0)
                ->where('a.read_party_id', $readPartyId)
                ->where('a.is_delete', 0);
        }

        //分页的时候如果有新的数据记录，设置一个查询的开始点，否则数据会重复
        if($firstReadPlanId > 0)
        {
            $db->where('d.SumUpID','<=',$firstReadPlanId);
        }
        $readPlans = $db->orderBy('d.SumUpID', "desc")
            ->groupBy('a.read_party_id','a.user_id')
            ->simplePaginate(20);

        foreach ($readPlans as $plan) {
            !isset($plan->department_id) && $plan->department_id = 0 ;
            $plan->FilePath1   = change_member_head_img($plan->FilePath1);
            $plan->BookReview1 = emoji_text_decode($plan->BookReview1);
            $plan->BookReview2 = emoji_text_decode($plan->BookReview2);
            $plan->BookReview3 = emoji_text_decode($plan->BookReview3);
            $plan->BookReview4 = emoji_text_decode($plan->BookReview4);
        }
        return $readPlans;
    }

    public function get_persons_zan($userPhone, $readId)
    {
        $userData = DB::table('memberinfo as m')
            ->leftJoin('read_party_member as rpm', 'rpm.user_id', '=', 'm.UserID')
            ->where(['UserName' => $userPhone, 'DeleteMark' => '1', 'rpm.read_party_id' => $readId])
            ->first(['m.YourName', 'm.UserID', 'rpm.read_party_id']);
        return $userData;
    }


    /**
     * 获得用户已经度过的书籍
     * @param string memberNumber 会员编号
     * */
    public function get_user_readed_book($memberNumber)
    {
        $SQL  = "SELECT * FROM (SELECT * from booksumup  WHERE  MemNumber = '$memberNumber' ORDER BY SumUpID DESC ) as a
                  GROUP BY BookID,MemNumber
                  ORDER BY SumUpID DESC";
        $info = DB::select($SQL);
        return $info;
    }

    /**
     * 统计用户已度过的书籍
     * @param string $memberNumber 用户编号
     * */
    public function count_user_readed_book($memberNumber)
    {
        $sql = "SELECT COUNT(*) as total FROM (
                SELECT SumUpID
                FROM booksumup
                WHERE MemNumber = '{$memberNumber}'
                GROUP  BY BookID,MemNumber ) AS a";

        $info = DB::select($sql);
        return $info[0]->total;
    }

    /**
     * 获得书籍PTT
     *
     * */
    public function get_book_ppt($bookId)
    {
        $ppts = DB::table('bookimg')
            ->select('FilePath1', 'SortNumber')
            ->where('BookID', $bookId)
            ->orderBy('SortNumber', 'asc')
            ->orderBy('AddDate', 'asc')
            ->get();
        foreach ($ppts as $ppt) {
            $ppt->FilePath1 = env('ADMIN_HOST') . $ppt->FilePath1;
        }
        return $ppts;
    }


    /**
     * 用户书籍的读书改进计划
     * @param number $readPlanId
     * */
    public function get_read_plan($readPlanId)
    {
        $readPlanInfo = DB::table('booksumup')
            ->where('SumUpID', $readPlanId)
            ->first();
        return $readPlanInfo;
    }


    /**
     * 获取用户某本书的读书改进计划
     * @param integer $bookId 书籍ID
     * @param integer $memberNumber 会员编号
     * */
    public function get_user_book_read_plan($bookId, $memberNumber)
    {
        $maxSumUpID      = DB::table('booksumup')->select(DB::raw('max(SumUpID) as max'))->where(['BookID' => $bookId, 'MemNumber' => $memberNumber])->first();
        $maxSumUpID      = $maxSumUpID->max;
        $readPlanComment = DB::table('booksumup')
            ->select('SumUpID',
                'BookReview1',
                'BookReview2',
                'BookReview3',
                'BookReview4')
            ->where('BookID', $bookId)
            ->where('MemNumber', $memberNumber)
            ->where('SumUpID', $maxSumUpID)
            ->orderBy('SumUpID', 'desc')
            ->first();
        return $readPlanComment;
    }


    /**
     * 获得国政书吧视频
     * @param int $type 视频类型
     * */
    public function get_gz_videos($type = 0)
    {
        $date   = current_date();
        $db     = DB::table('bookinfo')
            ->select($this->bookColum)
            ->where('video_type','gzsb')
            ->where('publish_date','<=',$date)
            ->orderBy('BookID','desc')
            ->where('DeleteMark',1);
        $type > 0 && $db->where('TypeID',$type);
        $videos = $db->paginate(10);
        foreach($videos as $video){
            $video->FilePath1 = env('HOST').$video->FilePath1;
        }
        return $videos;
    }


    /**
     * 获得国政书吧的首页图片
     * */
    public function get_gz_index_images()
    {
        $sql = "SELECT id gz_img_id,
                        banner_img AS img,
                        content_type AS `type`,
                        content_id,
                        1 AS is_show,
                        0 AS sort
                FROM banners
                WHERE banner_type = '2' ";
        $imgs = DB::select($sql);
        return $imgs;
    }




}